package com.zhan.ielts.tools;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.zhan.ielts.domain.WordPreRootSufix;

public class DictionaryUtils {
	private static Logger log = LoggerFactory.getLogger(DictionaryUtils.class);
	private static Connection conn = getConnection();

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		// List<String> list = getWordFromDictionary(0, 1);
		int result = updateCigen("test", "cc");
		log.info("result = " + result);

	}

	public static List<String> getWordFromDictionary(int from, int to) throws ClassNotFoundException, SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		// Connection conn = getConnection();
		if (conn == null) {
			conn = getConnection();
		}
		conn.setAutoCommit(false);
		stmt = conn.prepareStatement("select word from t_dictionary order by word limit  " + from + "," + to);
		rs = stmt.executeQuery();
		List<String> list = new ArrayList<String>();
		while (rs.next()) {
			String word = rs.getString(1);
			list.add(word);
			// System.out.println(word);
		}
		return list;
	}

	public static List<WordPreRootSufix> getWordPreRootSufix(int from, int to) throws ClassNotFoundException,
			SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		// Connection conn = getConnection();
		if (conn == null) {
			conn = getConnection();
		}
		conn.setAutoCommit(false);
		stmt = conn
				.prepareStatement("select part_word, type, word_class,word, word_translation,explaination,seq_no from t_etyma_affix order by type, part_word, word_class, word limit  "
						+ from + "," + to);
		rs = stmt.executeQuery();
		List<WordPreRootSufix> list = new ArrayList<WordPreRootSufix>();
		while (rs.next()) {
			WordPreRootSufix wordPreRootSufix = new WordPreRootSufix();
			wordPreRootSufix.setPartWord(rs.getString(1));
			wordPreRootSufix.setType(rs.getString(2));
			wordPreRootSufix.setWordClass(rs.getString(3));
			//wordPreRootSufix.setWord(rs.getString(4));
			wordPreRootSufix.setWordTranslation(rs.getString(5));
			wordPreRootSufix.setExplaination(rs.getString(6));
			wordPreRootSufix.setSeqNo(rs.getInt(7));

			list.add(wordPreRootSufix);
		}
		return list;
	}

	public static void updateWordPreRootSufix(List<WordPreRootSufix> list) throws ClassNotFoundException, SQLException {
		if (list == null || list.size() == 0)
			return;

		// Connection conn = getConnection();
		if (conn == null) {
			conn = getConnection();
		}
		conn.setAutoCommit(false);
		PreparedStatement stmt = conn
				.prepareStatement("replace into t_etyma_affix(part_word, type, word,word_class,word_translation,explaination,seq_no,root_affix_letter, root_affix_translation )VALUES (?, ?, ?, ?, ?, ? , ?, ?, ?)");

		for (int n = 0; n < list.size(); n++) {
			// System.out.println(list.get(n));
			WordPreRootSufix wordPreRootSufix = list.get(n);
			stmt.setString(1, wordPreRootSufix.getPartWord());
			stmt.setString(2, wordPreRootSufix.getType());
			//stmt.setString(3, wordPreRootSufix.getWord());
			stmt.setString(4, wordPreRootSufix.getWordClass());
			stmt.setString(5, wordPreRootSufix.getWordTranslation());
			stmt.setString(6, wordPreRootSufix.getExplaination());
			stmt.setInt(7, wordPreRootSufix.getSeqNo());
			stmt.setString(8, wordPreRootSufix.getRootAffixLetter());
			stmt.setString(9, wordPreRootSufix.getRootAffixTranslation());
			stmt.addBatch();
		}

		stmt.executeBatch();
		conn.commit();
	}

	public static int updateCigen(String word, String content) throws ClassNotFoundException, SQLException {
		// Connection conn = getConnection();
		if (conn == null) {
			conn = getConnection();
		}
		conn.setAutoCommit(false);
		PreparedStatement stmt = conn.prepareStatement("replace into t_word_cigen(word, content) values('" + word
				+ "','" + content + "')");
		int result = stmt.executeUpdate();
		conn.commit();
		return result;
	}

	public static Connection getConnection() {
		String className = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://172.19.0.188:3306/ieltsdb?zeroDateTimeBehavior=convertToNull";
		String username = "ieltsdba";
		String userpass = "zaq1_XSW2";
		Connection con = null;
		try {
			Class.forName(className);
			con = DriverManager.getConnection(url, username, userpass);
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
	}

}
